MySQL 学习（10）数据行结构和行溢出机制
这篇是直接使用之前《MySQL 的数据行格式和行溢出机制》 那篇笔记，因为和 《InnoDB存储引擎》 这本书的这部分重合了，所以直接使用之前的笔记代替了~
牢记 InnoDB 从磁盘中读取数据的最小单位是数据页，而你想得到的 id = xxx 的数据，就是这个数据页众多行中的一行。
ROW_FORMAT 是干什么的？
关于下面的具体格式的使用看文档 14.11 InnoDB 行格式
在看别人的项目提供的 SQL 的时候发现创建表时指定了这个 ROW_FORMAT 为：DYNAMIC
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
-- 略
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户表';
所以这个 DYNAMIC 是什么？
在看 ROW_FORMAT 之前，先来看下 InnoDB 的文件格式
InnoDB 的文件格式
在 InnoDB 1.0.X 之前，InnoDB 存储引擎提供了 Compact 和 Redundant 两种格式来存放行记录数据。Redundant 是 mysql5.0 版本之前的行记录存储方式，之后仍然支持这个格式是为了兼容之前版本的格式，5.1 之后很少用到了，因为 Compact 的结构设计比它好得多，Compact 格式消耗的磁盘空间和备份耗时更小，Redundant 相比之下大了一些。Compact 格式更适用于大多数的业务场景。
而在 InnoDB 1.0.X 版本开始又引入了新的文件格式（file format），以前支持 Compact 和 Redundant 格式称为 Antelope 文件格式，新引入的文件格式称为 Barracuda 文件格式。
Barracuda 文件格式下拥有两种新的行记录格式：Compressed 和 Dynamic，同时，Barracuda 文件格式也包括了 Antelope 所有的文件格式。
这样 Barracuda 文件格式支持 4 种 row_format：
Redundant、Compact、Compressed、Dynamic
而 Antelope 文件格式只支持 2 种 row_format：
Redundant、Compact
参数 innodb_file_format 用来指定文件格式，可以通过下面的方式来查看当前所使用的 InnoDB 存储引擎的文件格式：
show variables like 'innodb_file_format';
现在基本上都是 Barracuda。
静态表和动态表 ⭐
在 MySQL 中， 若一张表里面不存在 varchar、text、blob 等的字段的话，那么张这个表其实也叫静态表，即该表的 row_format 是 fixed，就是说每条记录所占用的字节一样。其优点读取快，缺点浪费额外一部分空间。
若一张表里面存在varchar、text、blob 等字段的话，那么张这个表其实也叫动态表，即该表的 row_format 是 dynamic，就是说每条记录所占用的字节是动态的。其优点节省空间，缺点增加读取的时间开销。
静态表，查询速度快，空间利用率不高 动态表，查询速度慢，空间利用率较高
所以，做搜索查询量大的表一般都以空间来换取时间，设计成静态表。
row_format 还有其他一些值：
- DEFAULT
- FIXED
- DYNAMIC
- COMPRESSED
- REDUNDANT
- COMPACT
修改行格式
ALTER TABLE table_name ROW_FORMAT = DEFAULT
修改过程导致：
fixed => dynamic: 这会导致 CHAR 变成 VARCHAR dynamic => fixed: 这会导致 VARCHAR 变成 CHAR
注意：只有在 MYSAM 的数据库引擎才支持这个 fixed 属性，上面只是介绍这个 ROW_FORMAT 选项的概念，在 InnoDB 的使用看下面
mysql> alter table oem_idlib row_format = fixed;
ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'ROW_TYPE'
紧凑的行格式
Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的数据行。
你品一品，让一个数据页中可以存放更多的数据行是一个多么激动人心的事，MySQL 以数据页为单位从磁盘中读数据，如果能做到让一个数据页中有更多的行，那岂不是使用的空间变少了，且整体的效率直线飙升？
官网介绍：Compact 能比 Redundant 格式节约 20% 的存储。
Compact 从 MySQL5.0 引入，MySQL5.1之后，行格式默认设置成 Compact 。所以本文描述的也是Compact格式。
紧凑的行格式长啥样？
你肯定晓得表中有的列允许为 null，有的列是变长的 varchar 类型。
那 Compact 行格式是如何组织描述这些信息的呢？如下图：
每部分包含的数据可能要比我上面标注的1、2、3还要多。为了给大家更直观的感受和理解我只是挑了一部分展示给大家看。
MySQL单行能存多大体量的数据？
在 MySQL 的设定中，单行数据最大能存储 65535byte 的数据（注意是 byte，而不是字符）
但是当你像下面这样创建一张数据表时却发生了错误：
MySQL 不允许创建一个长度为 65535byte 的列，因为数据页中每一行中都有我们上图提到的隐藏列。
所以将 varchar 的长度降低到 65532byte 即可成功创建该表
注意这里的 65535 指的是字节，而不是字符。
所以如果你将 charset 换成 utf8 这种编码格式，那 varchar(N) 中的 N 其实指的 N 个字符，而不是 N 个 byte。所以如果你像下面这样创建表就会报错。
假如 encode=utf8 时三个 byte 表示一个字符。那么 65535 / 3 = 21845 个字符。
Compact格式是如何做到紧凑的？
MySQL 每次进行随机的 IO 读
默认情况下，数据页的大小为 16KB。数据页中存储着数行。
那就意味着一个数据页中能存储越多的数据行，MySQL 整体的进行的 IO 次数就越少？性能就越快？
Compact 格式的实现思路是：当列的类型为VARCHAR、 VARBINARY、 BLOB、TEXT时，该列超过 768byte 的数据放到其他数据页中去。
看到这里来龙去脉是不是很清晰了呢？
MySQL 这样做，有效的防止了单个 varchar 列或者 Text 列太大 导致单个数据页中存放的行记录过少 而让 IO 飙升的窘境且占内存的。
什么时候会行溢出？
如果数据页默认大小为 16KB，换算成 byte：
16 * 1024 = 16384 byte
那你有没有发现，单页能存储的 16384byte 和单行最大能存储的 65535byte 差了好几倍呢？
也就是说，假如你要存储的数据行很大超过了 65532byte 那么你是写入不进去的。假如你要存储的单行数据小于 65535byte 但是大于 16384byte，这时你可以成功 insert，但是一个数据页又存储不了你插入的数据。这时肯定会行溢出！
其实在 MySQL 的设定中，发生行溢出并不是达到 16384byte 边缘才会发生。
对于 varchar、text 等类型的行。当这种列存储的长度达到几百 byte 时就会发生行溢。
行如何溢出？
还是看这张图：
在 MySQL 设定中，当 varchar 列长度达到 768byte 后，会将该列的前 768byte 当作当作 prefix 存放在行中，多出来的数据溢出存放到溢出页中，然后通过一个偏移量指针将两者关联起来，这就是行溢出机制。
为什么不使用两个数据页装一个数据？
不知道你有没有想过这样一个问题：
首先你肯定知道，MySQL使用的是 B+Tree 的聚簇索引，在这棵 B+Tree 中非叶子节点是只存索引不存数据，叶子节点中存储着真实的数据。同时叶子结点指向数据页。
那当单行存不下的时候，为啥不存储在两个数据页中呢？就像下图这样～。
单个节点存储下，我用多个节点存总行吧！说不定这样我的 B+Tee 还能变大长高（这其实是错误的想法）
这个错误的描述对应的脑图如下：
那MySQL不这样做的原因如下：
MySQL 想让一个数据页中能存放更多的数据行，至少也得要存放两行数据。否则就失去了 B+Tree 的意义。B+Tree 也退化成一个低效的链表。
你可以品上面那句话，他说的每个数据页至少要存放两行数据的意思不是说 数据页不能只存一行。你确确实实可以只往里面写一行数据，然后去吃个饭，干点别的。一直让这个数据页中只有一行数据。
这句话的意思是，当你往这个数据页中写入一行数据时，即使它很大将达到了数据页的极限，但是通过行溢出机制。依然能保证你的下一条数据还能写入到这个数据页中。
正确的脑图如下：
确保可以写入多条数据到数据页，这样可以节省 IO 的次数
DYNAMIC 格式是怎么存储的
dynamic 行格式类似于 COMPACT 行格式，但是使用 dynamic 行格式的表可以在 数据页 上存储比使用 COMPACT 行格式的表更多的数据，因为它们在行溢出数据的处理上却完全不同：
COMPACT 格式下，溢出列存储前 768 字节，而 dynamic 格式下，溢出的列只存储前 20 字节，一旦发生了行溢出，dynamic 其实就存储一个指针，数据都放在溢出页里，dynamic 将长字段（发生行溢出）完全放在溢出页存储。
COMPRESSED 的的存储方式
COMPRESSED 行格式类似于 COMPACT 行格式，但使用 COMPRESSED 行格式的表 可以在溢出页面上存储比使用 COMPACT 行格式的表更多的数据。这导致了比使用 COMPACT 行格式的表更高效的数据存储，特别是对于包含使用 VARBINARY、 VARCHAR、 BLOB 和 TEXT 数据类型的列的表。
即，它和 COMPACT 一样，超过 768byte 的数据放到其他数据页中去，只不过这个 COMPRESSED 多了个压缩的功能
Compressed 行记录格式的一个功能就是，存储在其中的行数据会以 zlib 的算法进行压缩，因此对于 BLOB、TEXT、VARCHAR 这类大长度类型的数据能进行非常有效的存储。
dynamic 的应用实例
生产 MYSQL 遇到的一个问题，在录入数据时，整行数据完全录不进去，报以下错：
Cause:java.sql.SQLException: com.taobao.tddl.common.exception.TddlException:java.sql.SQLException:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:Row size too large (> 8126). Changing
some columns to TEXT or BLOB or usingROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED
may help. In current row format,BLOB prefix of 768 bytes is stored inline.; nested exception
iscom.ibatis.common.jdbc.exception.NestedSQLException:
该表是一个产品介绍详情表，有 20 多个 TEXT 字段，刚好碰到了一个产品，每个字段录入的数据都很长，而 mysql 中有了个限制，一个页（这里 pagesize 是16K）必须至少存2行，也就是说每行的存储长度必须小于等于 8192，而这么多 TEXT 字段，一行肯定是存不下来，也就是会发生溢出，而即例发生溢出，每个列仍然会存储前 768 字节（该表的 row_formart 是 compact），字段一多还是超过了 8192，于是就报错，插不进了。
最后将表的 row_format 改为 dynamic 得以解决
ALTER TABLE table_name ROW_FORMAT = DEFAULT
所以，如果大家遇到一些表 TEXT 或 VARCHAR 大字段很多，又不好拆解时，可能需要考虑下溢出后列的长度了，如果溢出后列的长度还是太大，则要看一下表的 row_format
show table status like '%xxx%'\G
必要时需要将其实设置为 dynamic 如：
create table test(id int,name text,...... ) row_format=dynamic;
-- 或
alter table test row_format=dynamic;